Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Charting Your Data

Now that we’ve covered the preliminaries, we are ready to get to the fun stuff! Instead of creating a package that has graphics utilities, we’re going to use the NetCharts library, which is stored on the accompanying CD-ROM. The package on the CD is only an evaluation copy. Stop by http://www.netcharts.com to pick up the latest version (and some helpful documentation). We’ll use the table in Table 7.1 and a bar chart to display the salary information for our fictional company. Figure 7.1 shows the applet that is generated from the code in Listing 7.3. Remember, the code for this example can be found on the accompanying CD-ROM, or at The Coriolis Group Web site at http://www.coriolis.com/jdbc-book.


Figure 7.1  The bar chart applet.

Listing 7.3 Dynamically generating a bar chart from a database query—Part I.

/*
Example 7-1
*/
import java.awt.*;
import java.applet.Applet;
import java.sql.*;

public class example71 extends java.applet.Applet {
   String url;
  String Name;
  Connection con;
  TextArea OutputField = new TextArea(10,35);
  NFBarchartApp bar;
  // This is the bar chart class from the NetCharts package

public void init() {
  setLayout(new BorderLayout());
    url="jdbc:msql://elanor/jdbctest";
  // The URL for the database we wish to connect to

 ConnectToDB();
 // Connect to the database.

  add("North", OutputField);
 // Add the TextArea for showing the data to the user
  String columnData[] = getData("select * from Employee");
 // Run a query that goes and gets the complete table listing; we can put
 // any query here and would optimally want to get only the columns we
 // need.

  ShowFormattedData(columnData);
 // Show the data in the TextArea
  ShowChartData(columnData[3],columnData[2]);

// Now, pass the two data sets and create a bar chart
  add("Center", bar);
// And add the bar chart to the applet's panel
}

public void ShowFormattedData(String[] columnD ) {

int i;

for ( i=0; i< columnD.length; i++) {
  OutputField.appendText(columnD[i]+"\n");
}

}

public void ConnectToDB() {


  try {
    new imaginary.sql.iMsqlDriver();
    con = DriverManager.getConnection(url, "prpatel", "");
  }
  catch( Exception e ) {
    e.printStackTrace();
    System.out.println(e.getMessage());
  }

}

public void ShowChartData(String Data1, String Data2) {
try {
                bar = new NFBarchartApp(this);
                  // Instantiate the bar chart class

                bar.init();
                bar.start();
                  // Initialize it, and start it running.

                  // Below is where we load the parameters for the chart.
                  // See the documentation at the NetCharts Web site, or
                  // the CD-ROM for details.

                bar.loadParams(
                        "Header     = ('Salary Information');"+
                        "DataSets   = ('Salary', red);"+
                        "DataSet1   = "+ Data1 + ";"+
                        "BarLabels  = "+ Data2 + ";"+
                        "GraphLayout= HORIZONTAL;"+
                        "BottomAxis = (black, 'TimesRoman', 14, 0,
                        0,100000)"
                        );

                bar.loadParams ("Update");
                  // Tell the bar chart class we've put
                  // some new parameters in.

        } catch (Exception e) {
                System.out.println (e.getMessage());
        }

} // More to come following some comments…

The bar chart class from the NetCharts package uses a method to load the values for the chart. We have to define the labels and corresponding values, but this is generally straightforward. Because our data is formatted in a comma-delimited fashion, we don’t have to parse the data again to prepare it for use. In the next example (the pie chart example), we do have to parse it to put it in the proper format for the charting class to recognize it. Listing 7.4 picks up the code where we left off in Listing 7.3.

Listing 7.4 Dynamically generating a bar chart from a database query—Part II.

public String[] getData( String QueryLine ) {

  int columns, pos;
  String column[]=new String[4];
  boolean more;

 try {

      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(QueryLine);
      columns=(rs.getMetaData()).getColumnCount();
      column = new String[columns];

      // Initialize the columns to be blank
      for(pos=1; pos<=columns; pos++) {
        column[pos-1]="";
      }

      more=rs.next();

      while(more) {


        for (pos=1; pos<=columns; pos++) {
          column[pos-1]+=(rs.getString(pos));
        }

        more=rs.next();
        for (pos=1; pos<=columns; pos++) {
          if(more) {
            column[pos-1]+=(",");
          }
        }
      }
      stmt.close();

    }
    catch( Exception e ) {
      e.printStackTrace();
      System.out.println(e.getMessage());
    }

return column;
}

That’s it! We’ve successfully queried a database, formatted the resulting data, and created a bar chart to present a visual representation of the results. Listing 7.5 shows the generation of a pie chart, and Figure 7.2 shows the pie chart applet.


Figure 7.2  The pie chart applet.


Previous Table of Contents Next